package database;

import java.sql.*;
import java.util.ArrayList;

import oracle.jdbc.pool.OracleDataSource;


public class ReportOperations {

	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	
	public ReportOperations() {
		
		conn=openDB();
	}
	
	// open database connection
	public Connection openDB() {
		
		try {
			
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}
			
			conn = ods.getConnection();
			System.out.println("connected forgot.");
		} 
		catch (Exception e) {
			
			System.out.print("report Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	
	public ResultSet queryNumCustomer(int estate) 
	{
		try 
		{
			String queryString = "select cus.CUST_FNAME, r.est_id"
									+" from customer cus, road r"
									+" WHERE r.est_id =" +  estate 
									+" And r.ROAD_ID = cus.ROAD_ID"
									+" ORDER BY est_id";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	// return an array of all estates in table
	public ArrayList<String> queryEstateNames() {
		ArrayList<String> names = new ArrayList<String>();
		try {
			String queryString = "SELECT * FROM Estates1 ORDER BY est_id";
			prst = conn.prepareStatement(queryString);
			rst = prst.executeQuery();
			while (rst.next()) {
				names.add(rst.getString(2));
			}
		} catch (Exception e) {
			System.out.println(e);
		}
		return names;
	}
	
	public ResultSet queryOrdersEstate(int estate) 
	{
		try 
		{
			String queryString = "SELECT o.ord_num, e.est_price"
									+" from orders o, customer cus, road r, estates1 e"
									+" where o.cust_id = cus.cust_id" 
									+" and cus.ROAD_ID = r.ROAD_ID"
									+" and r.EST_ID = e.EST_ID"
									+" and e.EST_ID =" +  estate ;
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	public ResultSet queryOrderAmount(int day) 
	{
		try 
		{
			String queryString = "select *"
					+" from orderitem"
					+" WHERE day_id =" +  day 
					+" ORDER BY day_id";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	
	

	// close the connection to database
		public void closeDB() {
			try {
				stm.close();
				rst.close();
				conn.close();
				System.out.print("Connection closed forgot");
			} 
			catch (SQLException e){
				System.out.print("Could not close connection ");
				e.printStackTrace();
			}
		}
}
